package com.aorise.utils.excel;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * @Auther: zhouhao
 * @Date: 2019/6/21
 * @Description:
 */
public class ExcelImport {

    public static final String UPLOADTEACHEXCEL ="TEACHEXCEL";
    public Workbook readFile(InputStream inputStream,String fileName){
        Workbook wb  = null;
        Map<String,Object> map = new HashMap<>();
        boolean isE2007 = false;    //判断是否是excel2007格式
        if(fileName.endsWith("xlsx")){
            isE2007 = true;
        }
        try {
            InputStream input = inputStream;  //建立输入流
            //根据文件格式(2003或者2007)来初始化
            if(isE2007){
                wb = new XSSFWorkbook(input);
            }else{
                wb = new HSSFWorkbook(input);
            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 校检表头
     */
    public Map<String,Object> inspectHead(Sheet sheet){
        //所有的单元格信息
        List<CellRangeAddress> cras = getCombineCell(sheet);
        //存储行信息
        List<Map<String,Object>> irs = new ArrayList<>();
        //表头只有2行
        for(int i = 0; i < 2;i++){
            Row row = sheet.getRow(i);  //获取行
            Map<String,Object> map1 = new HashMap<String,Object>();
            //存储列信息
            List<Map<Integer,Object>> items = new ArrayList<>();
            if(isMergedRegion(sheet,i,0)){
                int lastRow = getRowNum(cras,sheet.getRow(i).getCell(0),sheet);
                for(;i<=lastRow;i++){
                    row = sheet.getRow(i);
                    int cellNum = row.getLastCellNum();//列数
                    Map<Integer,Object> map2 = new HashMap<Integer,Object>();
                    for(int ce=0;ce<cellNum;ce++){
                        map2.put(ce,getCellValue(row.getCell(ce)));
                    }
                    items.add(map2);
                }
                i--;
            }else{
                row = sheet.getRow(i);
                int cellNum = row.getLastCellNum();//列数
                Map<Integer,Object> map3 = new HashMap<Integer,Object>();
                for(int ce=0;ce<cellNum;ce++){
                    map3.put(ce,getCellValue(row.getCell(ce)));
                }
                items.add(map3);
            }
            map1.put("cell",items);
            irs.add(map1);
        }

        //获取目录个数，工种等级id
        List<Map<String,Object>> list = (ArrayList)irs.get(0).get("cell");//获取list的个数
        int catalogCount=0;
        Map<String,Object> save = new HashMap<>();
        Map<String,Object> saveGrade = new HashMap<>();
        for(int s=0;s<list.get(1).size();s++){
            if(list.get(1).get(s).toString().equals("")){
                catalogCount++;
            }else{
                saveGrade.put(s+"", list.get(1).get(s).toString());
            }
        }
        save.put("saveGrade", saveGrade);
        //取得工种父级
        save.put("catalogCount", catalogCount);
        String gzName = (String)list.get(0).get(catalogCount++);
        save.put("gzName", gzName);
        return save;
    }

    /**
     * 获取内部内容
     */
    public static List<Map<Integer,Object>> getBody(Sheet sheet){
        List<Map<Integer,Object>> all = new ArrayList<>();
        //所有的单元格信息
        List<CellRangeAddress> cras = getCombineCell(sheet);
        //存储行信息
        List<Map<String,Object>> irs = new ArrayList<>();
        int count = sheet.getLastRowNum()+1;//总行数
        //表头只有2行
        for(int i = 2; i < count;i++){
            Row row = sheet.getRow(i);  //获取行
            Map<String,Object> map1 = new HashMap<String,Object>();
            //存储列信息
            List<Map<Integer,Object>> items = new ArrayList<>();
            if(isMergedRegion(sheet,i,0)){
                int lastRow = getRowNum(cras,sheet.getRow(i).getCell(0),sheet);
                for(;i<=lastRow;i++){
                    row = sheet.getRow(i);
                    int cellNum = row.getLastCellNum();//列数
                    Map<Integer,Object> map2 = new HashMap<Integer,Object>();
                    for(int ce=0;ce<cellNum;ce++){
                        map2.put(ce,getCellValue(row.getCell(ce)));
                    }
                    items.add(map2);
                }
                i--;
            }else{
                row = sheet.getRow(i);
                int cellNum = row.getLastCellNum();//列数
                Map<Integer,Object> map3 = new HashMap<Integer,Object>();
                for(int ce=0;ce<cellNum;ce++){
                    map3.put(ce,getCellValue(row.getCell(ce)));
                }
                items.add(map3);
            }
            map1.put("cell",items);
            irs.add(map1);
        }

        for(int a=0;a<irs.size();a++){
            List<Map<Integer,Object>> list = (ArrayList)irs.get(a).get("cell");//获取list的个数
            if(list.size()>0){
                for(int b=0;b<list.size();b++){
                    all.add(list.get(b));
                }
            }
        }
        return all;
    }

    /**
     * 获取sheet中合并的单元格个数，并返回单元格list
     * @param sheet
     * @return List<CellRangeAddress>
     */
    public static List<CellRangeAddress> getCombineCell(Sheet sheet)
    {
        List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
        //获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        //遍历所有的合并单元格
        for(int i = 0; i<sheetmergerCount;i++)
        {
            //获得合并单元格保存进list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    private static boolean isMergedRegion(Sheet sheet,int row ,int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    return true;
                }
            }
        }
        return false;
    }

    private static int getRowNum(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet){
        int xr = 0;
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        for(CellRangeAddress ca:listCombineCell)
        {
            //获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)
            {
                if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)
                {
                    xr = lastR;
                }
            }

        }
        return xr;
    }

    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){
        if(cell == null) return "";
        if(cell.getCellType() == Cell.CELL_TYPE_STRING){
            return cell.getStringCellValue();
        }
        return "";
    }

    /**
     * 填充数据
     * @param list
     */
    public List<Map<Integer,Object>> fillData(List<Map<Integer,Object>> list){
        for(int i=0;i<list.size();i++){
            int ii=i;
            if(i==0){
                ii=i;
            }else{
                ii--;
            }
            for(int j=0;j<5;j++){
                if(list.get(i).get(j).toString().equals("")){
                    list.get(i).remove(j);
                    list.get(i).put(j, list.get(ii).get(j));
                }else{
                    break;
                }
            }
        }
        return list;
    }

    /**
     * 遍历list数组，实现数据拆分
     * @param map
     * @param list
     */
    public List<Map<String,Object>> splitData(Map<String,Object> map,List<Map<Integer,Object>> list){
        List<Map<String,Object>> all = new ArrayList<>();
        //获取总共的列数
        int colNum = list.get(0).size();
        //获取工种之前的列数
        Map<String,Object> catalogCount = (HashMap<String,Object>)map.get("saveGrade");
        int count = colNum-catalogCount.size();
        //1.循环list
        for(int i=0;i<list.size();i++){
            for(int j=0;j<count;j++){

                if(!list.get(i).get(j).toString().equals("")){
                    int jj=j;
                    Map<String,Object> col = new HashMap<>();
                    if((!list.get(i).get(j).toString().equals(""))&&j==0){
                        col.put("parent", 1);
                    }else{
                        col.put("parent", list.get(i).get(jj-1).toString());
                    }

                    col.put("name", list.get(i).get(j));
                    for(int a=(colNum-catalogCount.size());a<colNum;a++){
                        col.put(a+"", list.get(i).get(a));
                    }
                    all.add(col);
                }
            }
        }
        return all;
    }

    /**
     * 去重重复行
     * @param list
     */
    public List<Map<String,Object>> duplicateRemoval(List<Map<String,Object>> list){
        List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
        result.add(list.get(0));
        for(int i=0;i<list.size();i++){

            int f=0;
            for(int j=0;j<result.size();j++){
                String first= result.get(j).get("name").toString();
                String second= list.get(i).get("name").toString();
                if(first.equals(second)){
                    f=0;
                    break;
                }else{
                    f++;
                }
                if(f==result.size()){
                    result.add(list.get(i));
                }
            }
        }
        return result;
    }

    /**
     * 得到根目录
     * @param list
     */
    public String getRootCategory(List<Map<Integer,Object>> list){
        String add = "";
        Set<String> set = new LinkedHashSet<String>();
        for(int i=0;i<list.size();i++){
            set.add(list.get(i).get(0).toString());
        }

        for (String str : set) {
            add+="'"+str+"',";
        }
        return add.subSequence(0, add.length()-1).toString();
    }

    /**
     * 封装工种数据
     * @param list
     */
    public List<Map<String,Object>> packingIdentity(Map<String,Object> gz,Map<String,Object> map,List<Map<String,Object>> list){
        Map<String,Object> iscompulsoory = new HashMap<>();
        iscompulsoory.put("熟练掌握", 1);
        iscompulsoory.put("掌握", 1);
        iscompulsoory.put("熟悉", 0);
        iscompulsoory.put("了解", 0);
        iscompulsoory.put("不需要", 0);
        Map<String,Object> grade = (HashMap<String,Object>)map.get("saveGrade");
        for(int i=0;i<list.size();i++){
            List<Long> list1 = new ArrayList<>();
            for (String key : list.get(i).keySet()) {
                if(grade.containsKey(key)){
                    if(gz.containsKey(grade.get(key))){
                        list1.add(Long.valueOf(gz.get(grade.get(key)).toString()));
                    }
                }
            }
            List<Long> list2 = new ArrayList<>();
            int f=0;
            for (Object key : list.get(i).values()) {

                if(iscompulsoory.containsKey(key)){
                    list2.add(Long.valueOf(iscompulsoory.get(key).toString()));
                }
            }
            list.get(i).put("GZID", list1);//存储工种id
            list.get(i).put("GZIS", list2);//是否必修
        }
        return list;
    }

    /**
     * 将list解析成bean,进行存储信息（教材、工种）
     */
//    public void analysisList2bean(List<Map<String,Object>> list,CareerTeachManageService categoryService,UserToken userToken,UserGroupService userGroupService,Long userGroupId){
//        //map 用来存储父类
//        Map<String,Object> parent = new HashMap<>();
//        for(int i=0;i<list.size();i++){
//            CareerTeachMaterial ctm = new CareerTeachMaterial();
//            ctm.setName(list.get(i).get("name").toString());//教材名称
//            ctm.setSite(userToken.getCurrentSite());
//            //如果父类为1，则是等级目录
//            if(list.get(i).get("parent").toString().equals("1")){
//                CareerTeachMaterial parent1 = categoryService.findOne(1L);
//                ctm.setParent(parent1);
//            }else{
//                if(parent.containsKey(list.get(i).get("parent").toString())){
//                    Long parentId = Long.valueOf(parent.get(list.get(i).get("parent").toString()).toString());
//                    CareerTeachMaterial parent2 = categoryService.findOne(parentId);
//                    ctm.setParent(parent2);
//                }
//            }
//            UserGroup userGroup=userGroupService.findOne(userGroupId);
//            ctm.setUserGroup(userGroup);
//            Long maxSortId = null;
//            if (ctm.getParent() == null) {
//
//            } else {
//                maxSortId = categoryService.hasParentFindMaxOne(ctm.getParent().getId(),
//                        ctm.getUserGroup().getId());
//            }
//            if (maxSortId == null) {
//                ctm.setSortId((long) 1);
//            } else {
//                ctm.setSortId(maxSortId + 1);
//            }
//
//            //调用save方法
//            CareerTeachMaterial teach = categoryService.save(ctm);
//            //返回id值
//            parent.put(list.get(i).get("name").toString(), teach.getId());
//            //保存工种
//            List<Long> gzid = (ArrayList<Long>)list.get(i).get("GZID");
//            List<Long> gzis = (ArrayList<Long>)list.get(i).get("GZIS");
//            for(int j=0;j<gzid.size();j++){
//                Map<String,Object> isCom = new HashMap<>();
//                isCom.put("cace_material_id_com", teach.getId());
//                isCom.put("career_identiey_id", gzid.get(j));
//                isCom.put("is_compulsoory", gzis.get(j));
//                categoryService.insertIdentityIsCompulsory(isCom);
//            }
//        }
//    }
//    /**
//     * 检查重复
//     * @param sheet
//     * @return
//     */
//    public String checkRepeat(Sheet sheet){
//        TeachingMaterialImport ti = new TeachingMaterialImport();
//        //1.获取body内容
//        List<Map<Integer,Object>> bodyList = ti.getBody(sheet);
//        //2.将body中的null就行数据填充
//        List<Map<Integer,Object>> fillList = ti.fillData(bodyList);
//        return ti.getRootCategory(fillList);
//
//    }
//    public void executeAllMethod(Sheet sheet,Map<String,Object> head,Map<String,Object> gz,CareerTeachManageService categoryService,UserToken userToken,UserGroupService userGroupService,Long userGroupId){
//        TeachingMaterialImport ti = new TeachingMaterialImport();
//
//        //1.获取body内容
//        List<Map<Integer,Object>> bodyList = ti.getBody(sheet);
//        //2.将body中的null就行数据填充
//        List<Map<Integer,Object>> fillList = ti.fillData(bodyList);
//        //3.拆分表格中的数据
//        List<Map<String,Object>> splitList = ti.splitData(head,fillList);
//        //4.去除重复行
//        List<Map<String,Object>> duplicateList = ti.duplicateRemoval(splitList);
//        //5.封装其中包含的工种id及是否必修
//        List<Map<String,Object>> packingList =ti.packingIdentity(gz,head, duplicateList);
//        //6.将list封装成bean就行保存
//        ti.analysisList2bean(packingList,categoryService,userToken,userGroupService,userGroupId);
//    }
//
//    public static void main(String[] args) throws FileNotFoundException {
//        File file=new File("d://111.xlsx");
//        InputStream is = new FileInputStream(file);
//        TeachingMaterialImport ti = new TeachingMaterialImport();
//        Workbook wb = ti.readFile(is, "111.xlsx");
//        Sheet sheet = wb.getSheetAt(0);
//        Map<String,Object> map = ti.inspectHead(sheet);
//        List<Map<Integer,Object>> list = ti.getBody(sheet);
//        List<Map<Integer,Object>> fillList = ti.fillData(list);
//        // Set<String> set = ti.getRootCategory(fillList);
//        List<Map<String,Object>> splitList = ti.splitData(map,fillList);
//        List<Map<String,Object>> duplicateList = ti.duplicateRemoval(splitList);
//  		for(int i=0;i<fillList.size();i++){
//   			System.out.println(fillList.get(i));
//  		}
//   		List<Map<String,Object>> list2 = ti.splitData(map,ti.fillData(list));
//   		List<Map<String,Object>> list3 =ti.duplicateRemoval(list2);
        //List<Map<String,Object>> list4 =ti.packingIdentity(map, list3);
//   		for(int i=0;i<list4.size();i++){
//   			System.out.println(list4.get(i));
//   		}
        //ti.analysisList2bean(list4);
//    }
}
